from resource.context import *

class InsectQueryPage(PageRequest):
    def __init__(self,
                 page, size,
                 category_no=None,
                 insect_name=None,
                 insect_alias=None,
                 status=-1,
                 ):
        super().__init__(page, size)
        self.category_no = category_no
        self.insect_name = insect_name
        self.insect_alias = insect_alias
        self.status = status


class InsectService:
    def __init__(self, pool):
        self.pool = pool

    def cursor(self):
        conn = self.pool.get_connection()
        return conn.cursor(),conn

    def conn_close(self,cursor,conn):
        cursor.close()
        conn.close()

    def add(self, form):
        cursor,conn = self.cursor()

        category_id = form.get('category_id')
        insect_name = form.get('insect_name')
        insect_spell = form.get('insect_spell')
        scientific_name = form.get('scientific_name')
        insect_alias = form.get('insect_alias')
        img_url = form.get('img_url')
        insect_remark = form.get('insect_remark')
        insect_introduce = form.get('insect_introduce')
        status = 1
        latitude = form.get('latitude')
        longitude = form.get('longitude')
        distribution_area = form.get('distribution_area')
        living_record = form.get('living_record')
        prevent_measure = form.get('prevent_measure')

        insert_sql = 'insert into insect_info (category_id,insect_name,insect_spell,scientific_name,insect_alias,img_url,insect_remark,insect_introduce,status,latitude,longitude,distribution_area,living_record,prevent_measure,create_time,update_time) values ( %s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,now(),now())'
        cursor.execute(insert_sql,
                       (category_id, insect_name, insect_spell, scientific_name, insect_alias, img_url, insect_remark,
                        insect_introduce, status, latitude, longitude, distribution_area, living_record,
                        prevent_measure))
        cursor.connection.commit()
        self.conn_close(cursor,conn)

    def detail(self, id):
        cursor,conn = self.cursor()
        select_sql = ' select t.id,t.category_id,t.insect_name,t.insect_spell,t.scientific_name,t.insect_alias,t.img_url,t.insect_remark,' \
                     ' t.insect_introduce,t.status,t.latitude,t.longitude,t.distribution_area,t.life_cycle_1,t.life_cycle_2,t.life_cycle_3,' \
                     ' t.life_cycle_4,t.living_record,t.prevent_measure,t.create_time,t.update_time,t.create_by,t.update_by,c.category_no ' \
                     ' from insect_info t left join category c on t.category_id = c.id ' \
                     ' where t.id = %s'
        cursor.execute(select_sql,(id))
        data = cursor.fetchall()
        jsonData = fetch2Json(data, cursor.description)
        detail = jsonData[0]
        cateLevels = self.cateTree(self.cateNoSplit(detail['category_no']))
        for c in cateLevels:
            lv = len(c['category_no'])/CATEGORY_NO_LEVEL_LENGTH
            detail['lv'+str(int(lv)) + 'id'] = c['id']
            detail['lv'+str(int(lv)) + '_name'] = c['category_name']

        self.conn_close(cursor,conn)
        return detail

    def edit(self, form):
        cursor,conn = self.cursor()
        id = form.get('id')
        category_id = form.get('category_id')
        insect_name = form.get('insect_name')
        insect_spell = form.get('insect_spell')
        scientific_name = form.get('scientific_name')
        insect_alias = form.get('insect_alias')
        img_url = form.get('img_url')
        insect_remark = form.get('insect_remark')
        insect_introduce = form.get('insect_introduce')
        status = form.get('status')
        latitude = form.get('latitude')
        longitude = form.get('longitude')
        distribution_area = form.get('distribution_area')
        living_record = form.get('living_record')
        prevent_measure = form.get('prevent_measure')

        modify_sql = 'update insect_info t set t.category_id=%s, t.insect_name=%s, t.insect_spell=%s, t.scientific_name=%s, t.insect_alias=%s, t.img_url=%s, t.insect_remark=%s, t.insect_introduce=%s, t.status=%s, t.latitude=%s, t.longitude=%s, t.distribution_area=%s, t.living_record=%s, t.prevent_measure=%s,t.update_time=now() where id = %s '
        cursor.execute(modify_sql, (
            category_id, insect_name, insect_spell, scientific_name, insect_alias, img_url, insect_remark,
            insect_introduce, status, latitude, longitude, distribution_area, living_record,
            prevent_measure, id,
        ))
        cursor.connection.commit()
        self.conn_close(cursor,conn)

    def list(self, param: InsectQueryPage):
        cursor,conn = self.cursor()
        where = 'where 1=1 '
        queryParam = []
        if (param.insect_name is not None and len(param.insect_name) > 0):
            where += ' and t.insect_name like %s'
            queryParam.append('%' + param.insect_name + '%')
        if (param.category_no is not None and len(param.category_no) > 0):
            where += ' and c.category_no like %s'
            queryParam.append(param.category_no + '%')
        if (param.insect_alias is not None and len(param.insect_alias) > 0):
            where += ' and t.insect_alias like %s'
            queryParam.append('%' + param.insect_alias + '%')
        if (param.status > 0):
            where += ' and t.status = %s'
            queryParam.append(param.status)

        select_data_sql = 'select t.id,t.category_id,t.insect_name,t.insect_spell,t.scientific_name,t.insect_alias,t.img_url,t.insect_remark,t.insect_introduce,t.status,t.latitude,t.longitude,c.category_no ' \
                          'from insect_info t left join category c on t.category_id = c.id '

        select_data_sql += where
        select_data_sql += ' order by t.id desc '
        select_data_sql += ' limit ' + str(param.take()) + ',' + str(param.size)

        cursor.execute(select_data_sql, queryParam)
        data = cursor.fetchall()
        jsonData = fetch2Json(data, cursor.description)

        category_no_arr = []
        if len(jsonData) > 0:
            for i in jsonData:
                if (i['category_no'] is not None):
                    category_no_arr.append(i['category_no'])

            cateInfos = self.cateTree(category_no_arr)
            cateMap = {}
            for i in cateInfos:
                cateMap[i['category_no']] = i['category_name']

            for i in jsonData:
                if (i['category_no'] is not None):
                    nos = self.cateNoSplit(i['category_no'])
                    for no in nos:
                        lv = int(len(no) / CATEGORY_NO_LEVEL_LENGTH)
                        i['lv' + str(lv)] = cateMap[no]

        select_all_sql = 'select count(t.id) from insect_info t LEFT JOIN category c ON t.category_id = c.id ' + where
        cursor.execute(select_all_sql, queryParam)
        count = cursor.fetchone()[0]
        self.conn_close(cursor,conn)
        return PageResponse(jsonData, count, param.page, param.size)

    def delete(self, id):
        cursor,conn = self.cursor()
        modify_sql = 'delete from insect_info where id = %s'
        cursor.execute(modify_sql, (id))
        cursor.connection.commit()
        self.conn_close(cursor,conn)

    def cateNoSplit(self, no):
        result = []
        noLen = len(no) / CATEGORY_NO_LEVEL_LENGTH
        for index in range(int(noLen)):
            end = index * CATEGORY_NO_LEVEL_LENGTH + CATEGORY_NO_LEVEL_LENGTH
            result.append(no[0:end])
        return result

    def cateTree(self, cateNo=None):
        if cateNo is None:
            return []
        cateTreeNo = []
        for no in cateNo:
            noLen = len(no) / CATEGORY_NO_LEVEL_LENGTH
            for index in range(int(noLen)):
                end = index * CATEGORY_NO_LEVEL_LENGTH + CATEGORY_NO_LEVEL_LENGTH
                cateTreeNo.append(no[0:end])

        cursor,conn = self.cursor()
        select_data_sql = 'select id,category_no,category_name from category where category_no in (%s)' % ','.join(
            ['%s'] * len(cateTreeNo))
        cursor.execute(select_data_sql, (cateTreeNo))
        data = cursor.fetchall()
        jsonData = fetch2Json(data, cursor.description)
        self.conn_close(cursor,conn)
        return jsonData


    def match(self,filePath,user_id,idx):
        cursor,conn = self.cursor()
        select_data_sql = 'select id,insect_name,id_index from insect_info where id_index = %s '
        cursor.execute(select_data_sql, (idx))
        data = cursor.fetchall()

        result = 0
        id = -1
        name = '匹配失败'

        if(len(data) > 0):
            id = data[0][0]
            name = data[0][1]
            result = 1

        insert_sql = 'INSERT INTO `record`(`record_datetime`, `record_user_id`, `record_result`, `success`, `insect_id`,`match_file`)' \
                     ' VALUES (now(), %s, %s, %s, %s,%s);'
        cursor.execute(insert_sql,
                       (str(user_id),name,result,id,filePath))
        cursor.connection.commit()
        self.conn_close(cursor,conn)
        return id